<?php
session_start( );
?>
<?php
if (!isset($_SESSION['regid'] )) {
header("location: frmm.php");
}
require_once('connections/mysqlDB.php');
require('smarty_connect.php');
require("class.phpmailer.php"); 

$rid = $_SESSION['rid'];
$regid = $_SESSION['regid'];
$pastor=$_SESSION['pastor'];

$smarty = new smarty_connect;
$dbConn = new mysqli(HOST,USER,PWD,DB);
$dbConn->query("SET NAMES 'utf8'");

// send email notification

$stmt1 = $dbConn->query("select cname,first_name,last_name,email from registrant where regid = $regid");
$data=array();
while ($row = $stmt1->fetch_assoc()) {
$cname= $row['cname'];
$first_name= $row['first_name'];
$last_name= $row['last_name'];
$email= $row['email'];
};
$stmt1->close();
$confid=$_SESSION['conferenceID'];
//echo "rid=$rid regid=$regid email=$email confid=$confid";
// get registration data

$stmt1 = $dbConn->query("select cname,CONCAT(trim(first_name),' ',trim(last_name)) as fullname
,pastoral_staff,gender,
(select description from code where abbrev = relationship) as relationship,
(select description from code where abbrev = groups) as groups,
(select description from code where abbrev = special_need) as special_need,
meal1,meal2,meal3,meal4,meal5,meal6,meal7,meal8,session1,session2,
session3,session4,session5,session6,session7,session7,session8,session9,
session10,session11 
from registration_info where rid= $rid order by reginfoid");
$data=array();
while ($row = $stmt1->fetch_assoc()) {
$data[]=$row;
};
$stmt1->close();


$name="<tr>";
foreach ($data as $c) {
 foreach ($c as $key=>$values) {
  if ( ($key == 'cname') or ($key == 'fullname') or 
  ($key == 'gender') or ($key == 'relationship') or ($key == 'groups')
  or ($key == 'special_need')
  ) {
   $name = $name . "<td>$values</td>";
   }
}
$name = $name . "</tr><tr>";
}
$name = $name . "</tr>";

$stmt1 = $dbConn->query("SELECT d.cname as '中文姓名', CONCAT(trim(d.first_name),' ',trim(d.last_name)) as 'Name', (meal1) as '7/2 D' ,(meal2) as '7/3 L' ,
(meal3) as '7/3 D', (meal4) as '7/4 L', (meal5) as '7/4 D',
(meal6) as '7/5 L', (meal7) as '7/5 D' ,(meal8) as '7/6 L'
FROM registrant a, registration b, conference c, registration_info d
WHERE c.conference_id='$confid' and a.regid = b.regid
AND b.conference_id = c.conference_id
and b.rid = d.rid  and d.rid=$rid order by d.reginfoid ");
$data=array();
while ($row = $stmt1->fetch_assoc()) {
$data[]=$row;
};
$stmt1->close();

// get total lunch / dinner

$stmt1 = $dbConn->query("SELECT b.rid, b.conference_id, sum(meal2)+sum(meal4)+sum(meal6)+sum(meal8) as 'Lunch',
sum(meal1)+sum(meal3)+sum(meal5)+sum(meal7) as 'Dinner'
FROM registrant a, registration b, conference c, registration_info d
WHERE c.conference_id='$confid' and a.regid = b.regid
AND b.conference_id = c.conference_id
and b.rid = d.rid and d.rid=$rid
Group by b.rid, b.conference_id order by reginfoid");
while ($row = $stmt1->fetch_assoc()) {
$tot_lunch = $row["Lunch"];
$tot_dinner= $row["Dinner"];
};
$stmt1->close();

// check if reginstration_info has > 1 person

$stmt1 = $dbConn->query("SELECT count(*) as cnt from registration_info WHERE rid=$rid");
while ($row = $stmt1->fetch_assoc()) {
$morereginfoid = $row["cnt"];
};
$stmt1->close();

$meal="";
$t="<tr>";
$n=0;

foreach ($data as $c) {
 $n=$n+1;
 foreach ($c as $key=>$values) { 
   if ($n == 1) {$t=$t . "<th>" . $key . "</th>";}
   $meal = $meal . "<td width=\"2%\">" . $values . "</td>";
 }
 $meal = $meal . "<tr>";
}
$t=$t."</tr>";

$confirm = "";
$title = "<table width=\"850\"><tr><th width=\"9%\">中文姓名</th><th width=\"18%\">Name</th><th width=\"8%\">性別 Gender</th><th width=\"13%\">關係 Relationship</th><th width=\"17%\">組別 Group</th><th width=\"33%\">特別需要 Special Need</th></tr><tr>";
$message = $confirm . $title . $name . "</table>";
$mealplan = "<br><br>Meals Requested (L - Lunch, D - Dinner) <br><br><table width=\"850\"><tr>" . $t .  $meal . "</table>";
$total = "<br><br>Total Lunch: $tot_lunch &nbsp;&nbsp;&nbsp; Total Dinner: $tot_dinner<br>";

// send confirmation email

$my_t=getdate(date("U"));
$head1 ="$my_t[month] $my_t[mday], $my_t[year]";
$fullname=$_SESSION['fullname'];
//echo "rid=$rid  name=$fullname pastor=$pastor";
if (($_SESSION['pastor'] == 'N') or ($_SESSION['pastor'] == 'I') ) {
$head="親愛的 " .  $fullname . "， 您好！";
$head=$head . "<br><br>歡迎您來參加2009年夏令特會(7/2-7/6)！ 您已成功的修改2009夏令特會報名<br>，";
$head=$head . "這是您的確認號碼" .  $rid . "，請於報到時，帶此修改確認信至報到處辦理報到。<br><br>";
$head=$head . "願神在這一次特會大大的祝福您！<br><br>基督教慕主先鋒宣教構機 敬上<br>$my_t[month] $my_t[mday], $my_t[year]<br><br>";
$head=$head . "Thank you for registering for the 2009 Forerunner Summer Conference (7/2-7/6)! You have successfully modified your registration.<br>";
$head=$head . "Your confirmation number is " . $rid . ". Please make sure to bring this confirmation letter with you when you check-in for the conference.";
$head=$head . "<br><br>May the Lord bless you greatly through this conference!<br><br>Forerunner Mission Ministry (FRMM)<br>$my_t[month] $my_t[mday], $my_t[year]<br><br>";

}

if (($_SESSION['pastor'] == 'P') or ($_SESSION['pastor'] == 'P')  or ($_SESSION['pastor'] == 'M')) {
$head="親愛的牧者，您好！<br><br>歡迎您來參加2009年夏令特會(7/2-7/6)！您已成功的修改2009夏令特會報名<br>，這是您的確認號碼 " . $rid. "，請於報到時，帶此修改確認信<br>至特別來賓報到處辦理報到。<br><br>謝謝您在神國一切的擺上，願神在這一次特會大大的祝福您！<br><br>基督教慕主先鋒宣教構機 敬上<br><br>$my_t[month] $my_t[mday], $my_t[year]<br><br>";
$head=$head . "Beloved Pastor/Leader, <br><br>";
$head=$head . "Thank you for registering for the 2009 Forerunner Summer Conference (7/2-7/6)! You have successfully modified your registration.<br>";
$head=$head . "Your confirmation number is " . $rid . ". Please make sure to bring this confirmation letter with you when you check-in at the VIP registration table";
$head=$head . "<br><br>Thank you for all the work you’ve done for God’s kingdom. <br><br>May the Lord bless you greatly through this conference!<br><br>Forerunner Mission Ministry (FRMM)<br>$my_t[month] $my_t[mday], $my_t[year]<br><br>";

}

$to=$email;
$subject = "慕主特會報名修改確認 FRCC Conference Registration";
$footer="<br><br>請勿回覆此信， Please do not reply to this email confirmation.";

if ($morereginfoid > 0) {
$message = "
<html>
<head>
<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />
</head>
<body>
$head
$message
$mealplan
$total
$footer
</body>
</html>"; } else {
$message="<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" /></head>
<body>
您已成功的修改2009夏令特會報名，我們的資料顯示您的報名表上沒有任何人<br>
，很遺憾您不能來參加慕主先鋒特會，若是您還沒填寫報名人數，就麻煩您登入增加來特會的人數。<br>
<br><br>基督教慕主先鋒宣教構機 敬上<br>$my_t[month] $my_t[mday], $my_t[year]<br><br>
</body>
</html>";
}

// Always set content-type when sending HTML email

$headers = "MIME-Version: 1.0" . "\r\n";
$headers .= "Content-type:text/html;charset=utf-8" . "\r\n";// More headers
$headers .= 'From:FRMM Admin<frmm.conference@gmail.com>' . "\r\n";
$headers .= "\r\n";

$mail = mail($to,$subject,$message,$headers);

if(!$mail) {   
if ($stmt = $dbConn->prepare("UPDATE registration SET email_send = NULL, last_email_fail = NOW() 
      WHERE rid = ?")) {
    $stmt->bind_param('s', $rid);
    $stmt->execute(); 
    //printf ("Updated records: %d<br>", $stmt->affected_rows);
  }
  //echo 'Mailer error: ' . $mail->ErrorInfo;   
} else {   
if ($stmt = $dbConn->prepare("UPDATE registration SET email_send = NOW(), last_email_fail = NULL
      WHERE rid = ?")) {
    $stmt->bind_param('s', $rid);
    $stmt->execute(); 
    //printf ("Updated records: %d<br>", $stmt->affected_rows);
  }
  //echo "Message has been sent to $first_name $last_name @ $registrant_email.";   
} 

if ($morereginfoid  >0 ) {
// display confirmation

$stmt1 = $dbConn->query("select date_format(create_date,'%m/%d/%Y') as create_date from registration where rid= $rid");
$data=array();
while ($row = $stmt1->fetch_assoc()) {
$create_date=$row['create_date'];
};
$stmt1->close();

$stmt1 = $dbConn->query("select cname,first_name,last_name,first_time,pastoral_staff,
(select description from code where abbrev = relationship) as relationship,
gender,(select description from code where abbrev = groups) as groups,
(select description from code where abbrev = special_need) as special_need,
meal1,meal2,meal3,meal4,meal5,meal6,meal7,meal8,session1,session2,
session3,session4,session5,session6,session7,session7,session8,session9,
session10,session11 
from registration_info where rid= $rid");
$data=array();
while ($row = $stmt1->fetch_assoc()) {
$data[]=$row;
};
$stmt1->close();

//var_dump($_REQUEST);
$dbConn->close();
session_destroy();

$smarty->assign("regid",$regid);
$smarty->assign("rid",$rid);
$smarty->assign("email",$email);
$smarty->assign("create_date",$create_date);
$smarty->assign('data',$data);
$smarty->assign("tot_lunch",$tot_lunch);
$smarty->assign("tot_dinner",$tot_dinner);
$smarty->display('con_confirmation.tpl');
} else { //echo "cnt:$morereginfoid ";
//header("location: con_registration.php");
	  echo"<img src=\"wait.jpg\" width=\"444\" height=\"62\">"; 
?>
<script>
<!--
setTimeout("window.location.replace('con_registration.php')",3500);
-->
</script>
<?php }
?>
